--    Program: 		CAPAREXT_TRANSATCTION_TYPE.sql
--    Description:  Extract AR Transaction Type Information 
--    Revision:		1st     
--    Parameter: 	1. set of books id e.g. C999_SOB

set doc off;
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare  

  l_processed_count 	   	number;
  l_rejected_count  	   	number;
  l_err_msg       			varchar2(2000);
  
  l_utl_file  	      		utl_file.file_type;
  l_filename 		   		varchar2(255);
  l_pathname  		   		varchar2(100);
 
  l_ngo_sob			   		varchar2(20);
  l_code			      	varchar2(30);
  l_set_of_books_id			number;
  
  cursor cur_tra_types (p_set_of_books_id number) is
   select rctt.*
	from  ra_cust_trx_types_all rctt, 
	      hr_organization_information hoi
   where  hoi.org_information_context = 'Operating Unit Information'
   and    hoi.org_information3 = to_char(p_set_of_books_id)
   and    hoi.organization_id = rctt.org_id;
	
begin
  l_pathname := '/tmp';
  l_filename := 'CAPAR_TRANSACTION_TYPE.dat';   
  l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
  l_processed_count := 0;
  l_rejected_count  := 0;
                                           
  l_ngo_sob := '&1';
    
  begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);    
  exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
       --raise;
  end;  
  
  for s in cur_tra_types(l_set_of_books_id) loop
        
    utl_file.put_line(l_utl_file,
    					s.cust_trx_type_id || chr(9) ||
  						s.last_update_date || chr(9) ||
  						s.last_updated_by || chr(9) ||
  						s.creation_date || chr(9) ||
  						s.created_by || chr(9) ||
  						s.last_update_login || chr(9) ||
						s.post_to_gl || chr(9) ||
  						s.accounting_affect_flag || chr(9) ||
  						s.credit_memo_type_id || chr(9) ||
  						s.status || chr(9) ||
  						s.name || chr(9) ||
  						s.description || chr(9) ||
  						s.type || chr(9) ||
  						s.default_term || chr(9) ||
  						s.default_printing_option || chr(9) ||
  						s.default_status || chr(9) ||
  						s.gl_id_rev || chr(9) ||
  						s.gl_id_freight || chr(9) ||
  						s.gl_id_rec || chr(9) ||
  						s.subsequent_trx_type_id || chr(9) ||
  						s.set_of_books_id || chr(9) ||
  						s.attribute_category || chr(9) ||
  						s.attribute1 || chr(9) ||
  						s.attribute2 || chr(9) ||
  						s.attribute3 || chr(9) ||
  						s.attribute4 || chr(9) ||
  						s.attribute5 || chr(9) ||
  						s.attribute6 || chr(9) ||
  						s.attribute7 || chr(9) ||
  						s.attribute8 || chr(9) ||
  						s.attribute9 || chr(9) ||
  						s.attribute10 || chr(9) ||
  						s.allow_freight_flag || chr(9) ||
  						s.allow_overapplication_flag || chr(9) ||
  						s.creation_sign || chr(9) ||
  						s.end_date || chr(9) ||
  						s.gl_id_clearing || chr(9) ||
  						s.gl_id_tax || chr(9) ||
  						s.gl_id_unbilled || chr(9) ||
  						s.gl_id_unearned || chr(9) ||
  						s.start_date || chr(9) ||
  						s.tax_calculation_flag || chr(9) ||
  						s.attribute11 || chr(9) ||
  						s.attribute12 || chr(9) ||
  						s.attribute13 || chr(9) ||
  						s.attribute14 || chr(9) ||
  						s.attribute15 || chr(9) ||
  						s.natural_application_only_flag || chr(9) ||
  						s.org_id  || chr(9) ||
  						s.global_attribute1 || chr(9) ||
  						s.global_attribute2 || chr(9) ||
  						s.global_attribute3 || chr(9) ||
  						s.global_attribute4 || chr(9) ||
  						s.global_attribute5 || chr(9) ||
  						s.global_attribute6 || chr(9) ||
  						s.global_attribute7 || chr(9) ||
  						s.global_attribute8 || chr(9) ||
  						s.global_attribute9 || chr(9) ||
  						s.global_attribute10 || chr(9) ||
  						s.global_attribute11 || chr(9) ||
  						s.global_attribute12 || chr(9) ||
  						s.global_attribute13 || chr(9) ||
  						s.global_attribute14 || chr(9) ||
  						s.global_attribute15 || chr(9) ||
  						s.global_attribute16 || chr(9) ||
  						s.global_attribute17 || chr(9) ||
  						s.global_attribute18 || chr(9) ||
  						s.global_attribute19 || chr(9) ||
  						s.global_attribute20 || chr(9) ||
  						s.global_attribute_category || chr(9) ||
  						s.rule_set_id || chr(9) ||
  						s.signed_flag || chr(9) ||
  						s.drawee_issued_flag || chr(9) ||
  						s.magnetic_format_code || chr(9) ||
  						s.format_program_id || chr(9) ||
  						s.gl_id_unpaid_rec || chr(9) ||
  						s.gl_id_remittance || chr(9) ||
  						s.gl_id_factor || chr(9) ||
  						s.allocate_tax_freight);
    
    l_processed_count := l_processed_count + 1; 
  end loop;     
 
  -- record to log table 
  l_err_msg := '';
  if l_processed_count = 0 then
    l_rejected_count := l_rejected_count + 1;
    l_err_msg := l_ngo_sob||': Transaction Type not found' ;
  end if;    
      
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPAREXT_TRANSACTION_TYPE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
  
  commit;
  
  utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/


--exit
